﻿/******************************************************   
创建者: ljj
创建时间: 2017-10-14
修改:
修改时间: 
功能: 调拨结果明细
 ******************************************************/

using com.nm;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library;
using com.nm.library.Helper;
using CommodityTransferAPI.Model;
using george.Data;
using george.Helper;
using george.Web.Helper;
using Nancy.Extensions;
using NHibernateVertica;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;

namespace CommodityTransferAPI
{
    [Description("调拨结果明细")]
    public class MvJobAllMessageHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        /// Writer：ljj
        /// Create Date：2017-10-14
        [MustLogin]
        [Description("调拨结果明细列表")]
        [ReturnType(typeof(PaperData<MvJobAllMessageModel.ShowModel>))]
        public ApiResult GetMvJobAllMessageList(
            [ParamDescription("调出店铺")] string OutShopIDs = "", [ParamDescription("调入店铺")] string InShopIDs = "",
            [ParamDescription("货品年份")] long? YearNo = null, [ParamDescription("货品季节")] string SeasonID = "",
            [ParamDescription("系列")] string SeriesID = "", [ParamDescription("品类")] string ModelID = "",
            [ParamDescription("小品类")] string ItemID = "", [ParamDescription("货号")] string MaterialID = "",
            [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var outShopIDStr = OutShopIDs.GetString();
                var inShopIDStr = InShopIDs.GetString();
                //var dateTime = DateTime.Now.ToString("yyyy-MM-dd");
                var dateTime = "2017-12-09";//先取17号数据 2017-11-25
                var count = ReturnMvJobAllMessageCount(outShopIDStr, inShopIDStr, YearNo, SeasonID, SeriesID, ModelID,
                    ItemID, MaterialID, dateTime);
                var result = PaperData<MvJobAllMessageModel.ShowModel>.Create(count, Size, Page);
                var query = ReturnMvJobAllMessage(outShopIDStr, inShopIDStr, YearNo, SeasonID, SeriesID, ModelID,
                    ItemID, MaterialID, dateTime, Page, Size);
                var group = query.GroupBy(d => new
                {
                    d.ExtrSource,
                    d.CompanyID,
                    d.ProjectCode,
                    d.OutShopID,
                    d.InShopID,
                    d.MaterialID,
                    d.SuppleDate,
                    d.SizeTypeCode,
                    d.YearNo,
                    d.ProRetailPrice,
                    d.SeriesID,
                    d.SeriesName,
                    d.SeasonID,
                    d.SeasonName,
                    d.ItemID,
                    d.ItemName,
                    d.ColorID,
                    d.ColorName,
                    d.OutShopCode,
                    d.OutShopName,
                    d.OutSaleDays,
                    d.OutSKCCzpFlag,
                    d.OutTwoWeekSaleQty,
                    d.OutWeekSaleQty,
                    d.OutResultMcRate,
                    d.InShopCode,
                    d.InShopName,
                    d.InSaleDays,
                    d.InSKCCzpFlag,
                    d.InTwoWeekSaleQty,
                    d.InWeekSaleQty,
                    d.InISbreakSizeFlag,
                    d.InResultMcRate
                });
                var data = group.Select(d => new MvJobAllMessageModel.ShowModel
                {
                    ExtrSource = d.Key.ExtrSource,
                    CompanyID = d.Key.CompanyID,
                    ProjectCode = d.Key.ProjectCode,
                    OutShopID = d.Key.OutShopID,
                    InShopID = d.Key.InShopID,
                    MaterialID = d.Key.MaterialID,
                    SizeTypeCode = d.Key.SizeTypeCode,
                    SuppleDate = d.Key.SuppleDate,
                    YearNo = d.Key.YearNo,
                    ProRetailPrice = d.Key.ProRetailPrice,
                    SeriesID = d.Key.SeriesID,
                    SeriesName = d.Key.SeriesName,
                    SeasonID = d.Key.SeasonID,
                    SeasonName = d.Key.SeasonName,
                    ItemID = d.Key.ItemID,
                    ItemName = d.Key.ItemName,
                    ColorID = d.Key.ColorID,
                    ColorName = d.Key.ColorName,
                    OutShopCode = d.Key.OutShopCode,
                    OutShopName = d.Key.OutShopName,
                    OutSaleDays = d.Key.OutSaleDays,
                    OutSKCCzpFlag = d.Key.OutSKCCzpFlag,
                    OutTwoWeekSaleQty = d.Key.OutTwoWeekSaleQty,
                    OutWeekSaleQty = d.Key.OutWeekSaleQty,
                    OutResultMcRate = d.Key.OutResultMcRate,
                    InShopCode = d.Key.InShopCode,
                    InShopName = d.Key.InShopName,
                    InSaleDays = d.Key.InSaleDays,
                    InSKCCzpFlag = d.Key.InSKCCzpFlag,
                    InTwoWeekSaleQty = d.Key.InTwoWeekSaleQty,
                    InWeekSaleQty = d.Key.InWeekSaleQty,
                    InISbreakSizeFlag = d.Key.InISbreakSizeFlag,
                    InResultMcRate = d.Key.InResultMcRate,
                    SizeList = d.Select(r => new MvJobAllMessageModel.SizeModel
                    {
                        SizeID = r.SizeID,
                        SizeName = r.SizeName,
                        Qty = r.Qty
                    }).ToList()
                }).ToList();
                result.Items = data;
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex);
            }
        }

        /// <summary>
        /// 返回调拨结果明细总条数
        /// </summary>
        /// <param name="outShopIDStr"></param>
        /// <param name="inShopIDStr"></param>
        /// <param name="YearNo"></param>
        /// <param name="SeasonID"></param>
        /// <param name="SeriesID"></param>
        /// <param name="ModelID"></param>
        /// <param name="ItemID"></param>
        /// <param name="MaterialID"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2017-10-14
        public int ReturnMvJobAllMessageCount(string outShopIDStr, string inShopIDStr, long? YearNo, string SeasonID,
            string SeriesID, string ModelID, string ItemID, string MaterialID, string DateTimeStr)
        {
            try
            {
                #region

                var sql = $@"SELECT    count(1)
          FROM      ( SELECT    count(1)
                      FROM      ( SELECT    ExtrSource ,
                                            CompanyID ,
                                            OutShopID ,
                                            InShopID ,
                                            MaterialID ,
                                            ProjectCode ,
                                            SuppleDate
                                  FROM      ngoods.ngoods_Mv_JobAllMessage
                                  WHERE     ExtrSource = '{UserInfo.UserExtrSource}'
                                            AND CompanyID = '{UserInfo.UserCompanyId}'
                                            AND SuppleDate = '{DateTimeStr}'
                                            {
                        (string.IsNullOrEmpty(outShopIDStr) ? "" : $" AND OutShopID in {outShopIDStr} ")
                    }
                                            {
                        (string.IsNullOrEmpty(inShopIDStr) ? "" : $" AND InShopID in {inShopIDStr} ")
                    }
                                            {
                        (string.IsNullOrEmpty(MaterialID) ? "" : $" AND MaterialID = '{MaterialID}' ")
                    }
                                  GROUP BY  ExtrSource ,
                                            CompanyID ,
                                            OutShopID ,
                                            InShopID ,
                                            MaterialID ,
                                            ProjectCode ,
                                            SuppleDate
                                ) mes
                                LEFT JOIN ( SELECT  *
                                            FROM    nods.nods_erp_SD_Mat_Material
                                            WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                            {(YearNo.HasValue ? $" AND YearNo = '{YearNo.Value}' " : "")}
                                            {(string.IsNullOrEmpty(SeasonID) ? "" : $" AND SeasonID = '{SeasonID}' ")}
                                            {(string.IsNullOrEmpty(SeriesID) ? "" : $" AND SeriesID = '{SeriesID}' ")}
                                            {(string.IsNullOrEmpty(ModelID) ? "" : $" AND ModelID = '{ModelID}' ")}
                                            {(string.IsNullOrEmpty(ItemID) ? "" : $" AND ItemID = '{ItemID}' ")}
                                            {
                        (string.IsNullOrEmpty(MaterialID) ? "" : $" AND MaterialID = '{MaterialID}' ")
                    }
                                            
                                          ) mat ON mes.MaterialID = mat.MaterialID
                                                   AND mes.ExtrSource = mat.ExtrSource
                                LEFT JOIN ( SELECT  *
                                            FROM    nods.nods_erp_SD_Mat_Series
                                            WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                            {(string.IsNullOrEmpty(SeriesID) ? "" : $" AND SeriesID = '{SeriesID}' ")}
                                            
                                          ) ser ON mat.SeriesID = ser.SeriesID
                                                   AND mes.ExtrSource = ser.ExtrSource
                                LEFT JOIN ( SELECT  *
                                            FROM    nods.nods_erp_SD_Mat_Season
                                            WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                            {(string.IsNullOrEmpty(SeasonID) ? "" : $" AND SeasonID = '{SeasonID}' ")}
                                            
                                          ) sea ON sea.SeasonID = mat.SeasonID
                                                   AND mes.ExtrSource = sea.ExtrSource
                                LEFT JOIN ( SELECT  *
                                            FROM    nods.nods_erp_SD_Mat_Item
                                            WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                            {(string.IsNullOrEmpty(ItemID) ? "" : $" AND ItemID = '{ItemID}' ")}
                                            
                                          ) item ON mat.ItemID = item.ItemID
                                                    AND mes.ExtrSource = item.ExtrSource
                                LEFT JOIN ( SELECT  *
                                            FROM    nods.nods_erp_SD_Mat_Color
                                            WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                          ) col ON mat.ColorID = col.ColorID
                                                   AND mes.ExtrSource = col.ExtrSource
                                LEFT JOIN ( SELECT  *
                                            FROM    ndw.ndw_Shop
                                            WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                            {
                        (string.IsNullOrEmpty(outShopIDStr) ? "" : $" AND ShopID in {outShopIDStr} ")
                    }
                                            
                                          ) os ON mes.OutShopID = os.ShopID
                                                  AND mes.CompanyID = os.CompanyID
                                                  AND mes.ExtrSource = os.ExtrSource
                                LEFT JOIN ( SELECT  *
                                            FROM    ngoods.ngoods_Mv_SKCSaleRow
                                            WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                                    AND SuppleDate = '{DateTimeStr}'
                                            {
                        (string.IsNullOrEmpty(outShopIDStr) ? "" : $" AND ShopID in {outShopIDStr} ")
                    }
                                            {
                        (string.IsNullOrEmpty(MaterialID) ? "" : $" AND MaterialID = '{MaterialID}' ")
                    }
                                            
                                          ) orow ON mes.OutShopID = orow.ShopID
                                                    AND mes.CompanyID = orow.CompanyID
                                                    AND mes.ExtrSource = orow.ExtrSource
                                                    AND mes.MaterialID = orow.MaterialID
                                                    AND mes.SuppleDate = orow.SuppleDate
                                LEFT JOIN ( SELECT  *
                                            FROM    ngoods.ngoods_Mv_SatisfyRate
                                            WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                            {
                        (string.IsNullOrEmpty(outShopIDStr) ? "" : $" AND ShopID in {outShopIDStr} ")
                    }
                                            {(string.IsNullOrEmpty(ItemID) ? "" : $" AND ItemID = '{ItemID}' ")}
                                            
                                          ) orate ON mes.OutShopID = orate.ShopID
                                                     AND mes.CompanyID = orate.CompanyID
                                                     AND mes.ExtrSource = orate.ExtrSource
                                                     AND item.ItemID = orate.ItemID
                                LEFT JOIN ( SELECT  *
                                            FROM    ndw.ndw_Shop
                                            WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                            {
                        (string.IsNullOrEmpty(inShopIDStr) ? "" : $" AND ShopID in {inShopIDStr} ")
                    }
                                            
                                          ) ins ON mes.InShopID = ins.ShopID
                                                   AND mes.CompanyID = ins.CompanyID
                                                   AND mes.ExtrSource = ins.ExtrSource
                                LEFT JOIN ( SELECT  *
                                            FROM    ngoods.ngoods_Mv_SKCSaleRow
                                            WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                                    AND SuppleDate = '{DateTimeStr}'
                                            {
                        (string.IsNullOrEmpty(inShopIDStr) ? "" : $" AND ShopID in {inShopIDStr} ")
                    }
                                            {
                        (string.IsNullOrEmpty(MaterialID) ? "" : $" AND MaterialID = '{MaterialID}' ")
                    }
                                            
                                          ) irow ON mes.OutShopID = irow.ShopID
                                                    AND mes.CompanyID = irow.CompanyID
                                                    AND mes.ExtrSource = irow.ExtrSource
                                                    AND mes.MaterialID = irow.MaterialID
                                                    AND mes.SuppleDate = irow.SuppleDate
                                LEFT JOIN ( SELECT  *
                                            FROM    ngoods.ngoods_Mv_SatisfyRate
                                            WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                            {
                        (string.IsNullOrEmpty(inShopIDStr) ? "" : $" AND ShopID in {inShopIDStr} ")
                    }
                                            {(string.IsNullOrEmpty(ItemID) ? "" : $" AND ItemID = '{ItemID}' ")}
                                            
                                          ) irate ON mes.OutShopID = irate.ShopID
                                                     AND mes.CompanyID = irate.CompanyID
                                                     AND mes.ExtrSource = irate.ExtrSource
                                                     AND item.ItemID = irate.ItemID
                    GROUP BY     mes.ExtrSource ,
                                mes.CompanyID ,
                                mes.ProjectCode ,
                                mes.OutShopID ,
                                mes.InShopID ,
                                mes.MaterialID ,
                                mat.ProRetailPrice ,
                                mat.YearNo ,
                                mat.SeriesID ,
                                ser.SeriesName ,
                                mat.SeasonID ,
                                sea.SeasonName ,
                                item.ItemID ,
                                item.ItemName ,
                                os.ShopCode ,
                                os.ShopName ,
                                orow.SaleDays ,
                                orow.SKCCzpFlag ,
                                orow.TwoWeekSaleQty ,
                                orow.WeekSaleQty ,
                                orate.ResultMcRate ,
                                ins.ShopCode ,
                                ins.ShopName ,
                                irow.SaleDays ,
                                irow.SKCCzpFlag ,
                                irow.TwoWeekSaleQty ,
                                irow.WeekSaleQty ,
                                irow.ISbreakSizeFlag ,
                                irate.ResultMcRate
                    ) table1";

                #endregion

                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 返回调拨结果明细列表
        /// </summary>
        /// <param name="outShopIDStr"></param>
        /// <param name="inShopIDStr"></param>
        /// <param name="YearNo"></param>
        /// <param name="SeasonID"></param>
        /// <param name="SeriesID"></param>
        /// <param name="ModelID"></param>
        /// <param name="ItemID"></param>
        /// <param name="MaterialID"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2017-10-14
        public IList<MvJobAllMessageModel.ListModel> ReturnMvJobAllMessage(string outShopIDStr, string inShopIDStr,
            long? YearNo, string SeasonID, string SeriesID, string ModelID, string ItemID, string MaterialID, string DateTimeStr, int Page,
            int Size)
        {
            try
            {
                #region
                string extrSource = UserInfo.UserExtrSource;
                string companyId = UserInfo.UserCompanyId;
                var sql = $@"SELECT  table3.* ,
        allmes.SizeID ,
        allmes.SizeName ,
        allmes.Qty
FROM    ( SELECT    *
          FROM      ( SELECT    * ,
                                ROW_NUMBER() OVER ( ORDER BY ProjectCode, MaterialID, YearNo, SeasonID, SeriesID, ItemID ) AS Row
                      FROM      ( SELECT    mes.ExtrSource ,
                                            mes.CompanyID ,
                                            mes.ProjectCode ,
                                            mes.OutShopID ,
                                            mes.InShopID ,
                                            mes.MaterialID ,
                                            mes.SuppleDate ,
                                            mes.SizeTypeCode ,
                                            mat.ProRetailPrice ,
                                            mat.YearNo ,
                                            mat.SeriesID ,
                                            ser.SeriesName ,
                                            mat.SeasonID ,
                                            sea.SeasonName ,
                                            mat.ColorID ,
                                            col.ColorName ,
                                            item.ItemID ,
                                            item.ItemName ,
                                            os.ShopCode AS OutShopCode ,
                                            os.ShopName AS OutShopName ,
                                            orow.SaleDays AS OutSaleDays ,
                                            orow.SKCCzpFlag AS OutSKCCzpFlag ,
                                            orow.TwoWeekSaleQty AS OutTwoWeekSaleQty ,
                                            orow.WeekSaleQty AS OutWeekSaleQty ,
                                            orow.ResultMcRate AS OutResultMcRate ,
                                            ins.ShopCode AS InShopCode ,
                                            ins.ShopName AS InShopName ,
                                            irow.SaleDays AS InSaleDays ,
                                            irow.SKCCzpFlag AS InSKCCzpFlag ,
                                            irow.TwoWeekSaleQty AS InTwoWeekSaleQty ,
                                            irow.WeekSaleQty AS InWeekSaleQty ,
                                            irow.ISbreakSizeFlag AS InISbreakSizeFlag ,
                                            irow.ResultMcRate AS InResultMcRate 
                                  FROM      ( SELECT    ExtrSource ,
                                                        CompanyID ,
                                                        OutShopID ,
                                                        InShopID ,
                                                        MaterialID ,
                                                        SuppleDate ,
                                                        ProjectCode ,
                                                        SizeTypeCode
                                              FROM      ngoods.ngoods_Mv_JobAllMessage
                                              WHERE     ExtrSource = '{extrSource}'
                                                        AND CompanyID = '{companyId}'
                                                        AND SuppleDate = '{DateTimeStr}'
                                                        {
                        (string.IsNullOrEmpty(outShopIDStr) ? "" : $" AND OutShopID in {outShopIDStr} ")
                    }
                                                        {
                        (string.IsNullOrEmpty(inShopIDStr) ? "" : $" AND InShopID in {inShopIDStr} ")
                    }
                                                        {
                        (string.IsNullOrEmpty(MaterialID) ? "" : $" AND MaterialID = '{MaterialID}' ")
                    }
                                              GROUP BY  ExtrSource ,
                                                        CompanyID ,
                                                        OutShopID ,
                                                        InShopID ,
                                                        MaterialID ,
                                                        SuppleDate ,
                                                        ProjectCode ,
                                                        SizeTypeCode
                                            ) mes
                                            LEFT JOIN ( SELECT  *
                                                        FROM    nods.nods_erp_SD_Mat_Material
                                                        WHERE   ExtrSource = '{extrSource}'
                                                        {(YearNo.HasValue ? $" AND YearNo = '{YearNo.Value}' " : "")}
                                                        {(string.IsNullOrEmpty(SeasonID) ? "" : $" AND SeasonID = '{SeasonID}' ")}
                                                        {(string.IsNullOrEmpty(SeriesID) ? "" : $" AND SeriesID = '{SeriesID}' ")}
                                                        {(string.IsNullOrEmpty(ModelID) ? "" : $" AND ModelID = '{ModelID}' ")}
                                                        {(string.IsNullOrEmpty(ItemID) ? "" : $" AND ItemID = '{ItemID}' ")}
                                                        {
                        (string.IsNullOrEmpty(MaterialID) ? "" : $" AND MaterialID = '{MaterialID}' ")
                    }
                                            
                                                      ) mat ON mes.MaterialID = mat.MaterialID
                                                               AND mes.ExtrSource = mat.ExtrSource
                                            LEFT JOIN ( SELECT  *
                                                        FROM    nods.nods_erp_SD_Mat_Series
                                                        WHERE   ExtrSource = '{extrSource}'
                                                        {(string.IsNullOrEmpty(SeriesID) ? "" : $" AND SeriesID = '{SeriesID}' ")}
                                            
                                                      ) ser ON mat.SeriesID = ser.SeriesID
                                                               AND mes.ExtrSource = ser.ExtrSource
                                            LEFT JOIN ( SELECT  *
                                                        FROM    nods.nods_erp_SD_Mat_Season
                                                        WHERE   ExtrSource = '{extrSource}'
                                                        {(string.IsNullOrEmpty(SeasonID) ? "" : $" AND SeasonID = '{SeasonID}' ")}
                                            
                                                      ) sea ON sea.SeasonID = mat.SeasonID
                                                               AND mes.ExtrSource = sea.ExtrSource
                                            LEFT JOIN ( SELECT  *
                                                        FROM    nods.nods_erp_SD_Mat_Item
                                                        WHERE   ExtrSource = '{extrSource}'
                                                        {(string.IsNullOrEmpty(ItemID) ? "" : $" AND ItemID = '{ItemID}' ")}
                                            
                                                      ) item ON mat.ItemID = item.ItemID
                                                                AND mes.ExtrSource = item.ExtrSource
                                            LEFT JOIN ( SELECT  *
                                                        FROM    nods.nods_erp_SD_Mat_Color
                                                        WHERE   ExtrSource = '{extrSource}'
                                                      ) col ON mat.ColorID = col.ColorID
                                                               AND mes.ExtrSource = col.ExtrSource
                                            LEFT JOIN ( SELECT  *
                                                        FROM    ndw.ndw_Shop
                                                        WHERE   ExtrSource = '{extrSource}'
                                                                AND CompanyID = '{companyId}'
                                                        {
                        (string.IsNullOrEmpty(outShopIDStr) ? "" : $" AND ShopID in {outShopIDStr} ")
                    }
                                            
                                                      ) os ON mes.OutShopID = os.ShopID
                                                              AND mes.CompanyID = os.CompanyID
                                                              AND mes.ExtrSource = os.ExtrSource
                                            LEFT JOIN ( SELECT  *
                                                        FROM    ngoods.ngoods_Mv_SKCSaleRow
                                                        WHERE   ExtrSource = '{extrSource}'
                                                                AND CompanyID = '{companyId}'
                                                                AND SuppleDate = '{DateTimeStr}'
                                                        {
                        (string.IsNullOrEmpty(outShopIDStr) ? "" : $" AND ShopID in {outShopIDStr} ")
                    }
                                                        {
                        (string.IsNullOrEmpty(MaterialID) ? "" : $" AND MaterialID = '{MaterialID}' ")
                    }
                                            
                                                      ) orow ON mes.OutShopID = orow.ShopID
                                                                AND mes.CompanyID = orow.CompanyID
                                                                AND mes.ExtrSource = orow.ExtrSource
                                                                AND mes.MaterialID = orow.MaterialID
                                                                AND mes.SuppleDate = orow.SuppleDate
                                            LEFT JOIN ( SELECT  *
                                                        FROM    ndw.ndw_Shop
                                                        WHERE   ExtrSource = '{extrSource}'
                                                                AND CompanyID = '{companyId}'
                                                        {
                        (string.IsNullOrEmpty(inShopIDStr) ? "" : $" AND ShopID in {inShopIDStr} ")
                    }
                                            
                                                      ) ins ON mes.InShopID = ins.ShopID
                                                               AND mes.CompanyID = ins.CompanyID
                                                               AND mes.ExtrSource = ins.ExtrSource
                                            LEFT JOIN ( SELECT  *
                                                        FROM    ngoods.ngoods_Mv_SKCSaleRow
                                                        WHERE   ExtrSource = '{extrSource}'
                                                                AND CompanyID = '{companyId}'
                                                                AND SuppleDate = '{DateTimeStr}'
                                                        {
                        (string.IsNullOrEmpty(inShopIDStr) ? "" : $" AND ShopID in {inShopIDStr} ")
                    }
                                                        {
                        (string.IsNullOrEmpty(MaterialID) ? "" : $" AND MaterialID = '{MaterialID}' ")
                    }
                                            
                                                      ) irow ON mes.InShopID = irow.ShopID
                                                                AND mes.CompanyID = irow.CompanyID
                                                                AND mes.ExtrSource = irow.ExtrSource
                                                                AND mes.MaterialID = irow.MaterialID
                                                                AND mes.SuppleDate = irow.SuppleDate
                               WHERE 1=1 
                                                                 {(YearNo.HasValue ? $" AND mat.YearNo = '{YearNo.Value}' " : "")}
                                                        {(string.IsNullOrEmpty(SeasonID) ? "" : $" AND mat.SeasonID = '{SeasonID}' ")}
                                                        {(string.IsNullOrEmpty(SeriesID) ? "" : $" AND mat.SeriesID = '{SeriesID}' ")}
                                                        {(string.IsNullOrEmpty(ModelID) ? "" : $" AND mat.ModelID = '{ModelID}' ")}
                                                        {(string.IsNullOrEmpty(ItemID) ? "" : $" AND mat.ItemID = '{ItemID}' ")}
                                                        {
                        (string.IsNullOrEmpty(MaterialID) ? "" : $" AND mat.MaterialID = '{MaterialID}' ")
                    }
                                GROUP BY     mes.ExtrSource ,
                                            mes.CompanyID ,
                                            mes.ProjectCode ,
                                            mes.OutShopID ,
                                            mes.InShopID ,
                                            mes.MaterialID ,
                                            mes.SuppleDate ,
                                            mes.SizeTypeCode ,
                                            mat.ProRetailPrice ,
                                            mat.YearNo ,
                                            mat.SeriesID ,
                                            ser.SeriesName ,
                                            mat.SeasonID ,
                                            sea.SeasonName ,
                                            mat.ColorID ,
                                            col.ColorName ,
                                            item.ItemID ,
                                            item.ItemName ,
                                            os.ShopCode ,
                                            os.ShopName ,
                                            orow.SaleDays ,
                                            orow.SKCCzpFlag ,
                                            orow.TwoWeekSaleQty ,
                                            orow.WeekSaleQty ,
                                            orow.ResultMcRate ,
                                            ins.ShopCode ,
                                            ins.ShopName ,
                                            irow.SaleDays ,
                                            irow.SKCCzpFlag ,
                                            irow.TwoWeekSaleQty ,
                                            irow.WeekSaleQty ,
                                            irow.ISbreakSizeFlag ,
                                            irow.ResultMcRate
                                ) table1
                    )table2
          WHERE     Row BETWEEN ( {Page} - 1 ) * {Size} + 1 AND {Page} * {Size}
        ) table3
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_Mv_JobAllMessage
                    WHERE   ExtrSource = '{extrSource}'
                            AND CompanyID = '{companyId}'
                            AND SuppleDate = '{DateTimeStr}'
                            {(string.IsNullOrEmpty(outShopIDStr) ? "" : $" AND OutShopID in {outShopIDStr} ")}
                            {(string.IsNullOrEmpty(inShopIDStr) ? "" : $" AND InShopID in {inShopIDStr} ")}
                            {(string.IsNullOrEmpty(MaterialID) ? "" : $" AND MaterialID = '{MaterialID}' ")}
                    
                  ) allmes ON table3.ExtrSource = allmes.ExtrSource
                              AND table3.CompanyID = allmes.CompanyID
                              AND table3.ProjectCode = allmes.ProjectCode
                              AND table3.OutShopID = allmes.OutShopID
                              AND table3.InShopID = allmes.InShopID
                              AND table3.MaterialID = allmes.MaterialID
                              AND table3.SuppleDate = allmes.SuppleDate
                              AND table3.SizeTypeCode = allmes.SizeTypeCode";

                #endregion

                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<MvJobAllMessageModel.ListModel>.ConvertToModel(dt);
                return list.ToList();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：ljj
        /// Create Date：2017-10-14
        [MustLogin]
        [JsonType]
        [Description("修改")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult UpdateMvJobAllMessage(MvJobAllMessageModel.ModifyListModel param)
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            var uid = Guid.NewGuid().ToString("N");
            var tempTableName = $@"ngoods.temp{uid}";
            try
            {

                if (param == null) return Error("参数对象为空");
                if (param.List != null)
                {
                    var delList = param.List.Select(d => d.SizeList.Where(r => r.Qty.HasValue && r.Qty.Value == 0)
                        .Select(
                            r => new MvJobAllMessageModel.CoreModel
                            {
                                CompanyID = d.CompanyID,
                                ExtrSource = d.ExtrSource,
                                InShopID = d.InShopID,
                                OutShopID = d.OutShopID,
                                MaterialID = d.MaterialID,
                                ProjectCode = d.ProjectCode,
                                SuppleDate = Convert.ToDateTime(d.SuppleDate).ToString("yyyy-MM-dd"),
                                SizeTypeCode = d.SizeTypeCode,
                            })).SelectMany(d => d).DistinctBy(d => new
                            {
                                d.CompanyID,
                                d.ExtrSource,
                                d.InShopID,
                                d.OutShopID,
                                d.MaterialID,
                                d.ProjectCode,
                                d.SuppleDate,
                                d.SizeTypeCode,
                            }).ToList();
                    DelMvJobAllMessage(delList);
                    var result = param.List.Select(d => d.SizeList.Where(r => r.Qty.HasValue && r.Qty.Value > 0).Select(
                        r => new MvJobAllMessageModel.SaveModel
                        {
                            CompanyID = d.CompanyID,
                            ExtrSource = d.ExtrSource,
                            InShopID = d.InShopID,
                            OutShopID = d.OutShopID,
                            MaterialID = d.MaterialID,
                            ProjectCode = d.ProjectCode,
                            SuppleDate = Convert.ToDateTime(d.SuppleDate).ToString("yyyy-MM-dd"),
                            SizeTypeCode = d.SizeTypeCode,
                            Qty = r.Qty,
                            SizeID = r.SizeID,
                            SizeName = r.SizeName,
                        })).SelectMany(d => d);
                    var dt = DataTableHelper.LinqToDataTable(result);
                    MergeMvJobAllMessage(dt, tempTableName);
                    //调用Python重新计算数据
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error(ex);
            }
        }


        /// Writer：梁家健
        /// Create Date：2017-05-05
        [MustLogin]
        [Description("创建唯一码或流行色")]
        [ReturnType(typeof(PaperData<MvJobAllMessageModel.ShowModel>))]
        public ApiResult CalculateMvJobAllMessage()
        {
            try
            {
                var dateTime = DateTime.Now.ToString("yyyy-MM-dd");
                var server = IpAddressHelper.WebConfig("python-server"); //"192.168.10.127";
                var username = IpAddressHelper.WebConfig("python-server-user"); //"bi";
                var port = Convert.ToInt32(IpAddressHelper.WebConfig("python-server-port")); //"22";
                var password = IpAddressHelper.WebConfig("python-server-pass"); //"Km_etl2017";
                var command =
                    $"python /home/bi/scripts/python/ngoods/transfer/SP_Move_CalcFullRate.py {UserInfo.UserCompanyId} {dateTime}";
                // /usr/bin/python 
                var result = ConnLinux(server, username, port, password, command);
                return result.Contains("success") ? GetMvJobAllMessageList() : Error(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// Writer：ljj
        /// Create Date：2017-10-23
        public void DelMvJobAllMessage(IList<MvJobAllMessageModel.CoreModel> list)
        {
            try
            {
                const string sql = @" ( SuppleDate='{0}' and ProjectCode='{1}' and OutShopID='{2}' and InShopID='{3}' and MaterialID='{4}' and SizeTypeCode='{5}' ) {6}";
                var sb = new StringBuilder($@" DELETE
FROM    ngoods.ngoods_Mv_JobAllMessage
WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}'
        AND ");
                var finalSB = new StringBuilder();
                var i = 1;
                foreach (var item in list)
                {
                    sb.AppendLine(string.Format(sql, item.SuppleDate, item.ProjectCode, item.OutShopID, item.InShopID, item.MaterialID, item.SizeTypeCode,
                        (i == list.Count || i % 1000 == 0) ? ";" : " OR "));
                    if (i % 1000 == 0)
                    {
                        finalSB.AppendLine(sb.ToString());
                        sb = new StringBuilder($@" DELETE
FROM    ngoods.ngoods_Mv_JobAllMessage
WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}'
        AND ");
                    }
                    else
                    {
                        if (i == list.Count)
                        {
                            finalSB.AppendLine(sb.ToString());
                        }
                    }
                    i++;
                }
                var cmd = Helper.GetSqlStringCommand(finalSB.ToString());
                Helper.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        public void MergeMvJobAllMessage(DataTable dt, string TempTableName)
        {
            try
            {
                const string onConditions = @"targetTable.CompanyID=sourceTable.CompanyID
    and targetTable.ExtrSource=sourceTable.ExtrSource
	and targetTable.SuppleDate=sourceTable.SuppleDate
	and targetTable.ProjectCode=sourceTable.ProjectCode
	and targetTable.OutShopID=sourceTable.OutShopID
	and targetTable.InShopID=sourceTable.InShopID
	and targetTable.MaterialID=sourceTable.MaterialID
	and targetTable.SizeTypeCode=sourceTable.SizeTypeCode
	and targetTable.SizeID=sourceTable.SizeID";
                var setConditions = $@"Qty=sourceTable.Qty";
                const string insertConditions =
                    @"ExtrSource,CompanyID,SuppleDate,ProjectCode,OutShopID,InShopID,MaterialID,SizeTypeCode,SizeID,SizeName,Qty";
                const string valuesConditions =
                    @"sourceTable.ExtrSource,sourceTable.CompanyID,sourceTable.SuppleDate,sourceTable.ProjectCode,sourceTable.OutShopID,sourceTable.InShopID,sourceTable.MaterialID,sourceTable.SizeTypeCode,sourceTable.SizeID,sourceTable.SizeName,sourceTable.Qty";
                Helper.Merge(TempTableName, "ngoods.ngoods_Mv_JobAllMessage", dt, onConditions, setConditions,
                    insertConditions,
                    valuesConditions, true);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        /// <summary>
        /// Writer：ljj
        /// Create Date：2017-10-14
        /// </summary>
        /// <param name="whereStr">查询条件</param>
        /// <returns></returns>
        public DataTable ExportMvJobAllMessage(string whereStr)
        {
            try
            {
                #region  
                string CompanyID = UserInfo.UserCompanyId;
                string ExtrSource = UserInfo.UserExtrSource;
                //string datestr = DateTime.Now.ToString("yyyy-MM-dd");
                string datestr = "2017-12-09";
                var sql = $@"
SELECT * FROM (
	SELECT A.*,B.YearNo,B.SeasonID,B.SeriesID,B.ModelID,B.ItemID from (
		SELECT CompanyID,OutStockCode,OutStockName,InStockCode,InStockName,MaterialID,InShopID,OutShopID,JM
		F,S,M,L,XL,XXL,N3XL,N4XL,N27,N28,N29,N30,N31,N32,N33,N34,N35,N36,N37,N38,N39,N40,N41,N42,N43,N44,N45
		FROM ngoods.vw_ngoods_JobAllMessage 
		WHERE ExtrSource = '{ExtrSource}' AND CompanyID = '{CompanyID}' AND SuppleDate = '{datestr}'
	) A INNER JOIN (
		select MaterialID,YearNo,SeasonID,SeriesID,ModelID,ItemID 
		from nods.nods_erp_SD_Mat_Material 
		WHERE ExtrSource = '{ExtrSource}'
	)B ON A.MaterialID = B.MaterialID
)A 
WHERE 1=1 {whereStr}
ORDER BY MaterialID";

                #endregion

                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return dt;
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                throw (ex);
            }
        }
    }
}
